package io.renren.common.utils;

import io.renren.common.enums.IDictEnum;
import io.renren.common.template.excel.ExcelField;
import io.renren.common.template.excel.ExcelSheet;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;

import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * ExcelExportUtils contains a lot of common methods to export excel
 *
 * @author Harway
 * @version v1.0
 */
public class ExcelExportUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtils.class);

    public static final Long MAX_XSSF_ROW = 5000L;

    private static final String EXCEL_NAME_DATE_PATTERN = "yyyyMMddHHmmss";

    public static Workbook generateWorkbook(List<?> list, Class<?> sheetClass) {

        Workbook workbook = new XSSFWorkbook();
        ExcelSheet exportSheet = sheetClass.getAnnotation(ExcelSheet.class);
        String sheetName;
        if (exportSheet != null) {
            sheetName = exportSheet.name();
        } else {
            sheetName = sheetClass.getSimpleName();
        }
        Sheet sheet = workbook.createSheet(sheetName);
        ExcelStyle exportUtil = new ExcelStyle(workbook, sheet);
        CellStyle headStyle = exportUtil.getHeadStyle();
        CellStyle bodyStyle = exportUtil.getBodyStyle();
        // 构建表头
        Row headRow = sheet.createRow(0);
        Cell cell;

        // 选取配有注解的字段
        List<Field> fields = getFieldsWithAnnotation(sheetClass);

        for (int i = 0; i < fields.size(); i++) {
            cell = headRow.createCell(i);
            cell.setCellStyle(headStyle);
            Field field = fields.get(i);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            String fieldName = (excelField != null && StringUtils.isNotBlank(excelField.fieldName()) && excelField.fieldName().trim().length() > 0) ? excelField.fieldName() : field.getName();
            cell.setCellValue(fieldName);
        }

        // 构建表体数据
        for (int j = 0; j < list.size(); j++) {
            Object rowData = list.get(j);
            Row bodyRowX = sheet.createRow(j + 1);
            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                Object fieldValue = getFieldValue(sheetClass, field, rowData);
                String fieldValueString = formatValue(field, fieldValue);
                Cell xCell = bodyRowX.createCell(i);
                xCell.setCellStyle(bodyStyle);
                xCell.setCellValue(fieldValueString);
            }
        }

        // 宽度自适应
        for (int k = 0; k < fields.size(); k++) {
            sheet.autoSizeColumn(k);
        }
        ExcelExportUtils.setSizeColumn(sheet, fields.size());

        return workbook;
    }

    // 选取配有注解的字段
    private static List<Field> getFieldsWithAnnotation(Class<?> sheetClass) {
        List<Field> fields = new ArrayList<>();
        // 选取配有注解的字段
        if (sheetClass.getDeclaredFields() != null && sheetClass.getDeclaredFields().length > 0) {
            for (Field field : sheetClass.getDeclaredFields()) {
                if (field.getAnnotation(ExcelField.class) == null) {
                    continue;
                }
                fields.add(field);
            }
            // 排序
            fields.sort(Comparator.comparingInt(field -> field.getAnnotation(ExcelField.class).order()));
        }
        return fields;
    }

    /**
     * 获取实例指定字段的值
     * @param sheetClass 实例的Class类对象
     * @param field 指定的字段
     * @param target 目标实例
     * @return Object
     */
    private static Object getFieldValue(Class<?> sheetClass, Field field, Object target) {
        PropertyDescriptor propertyDescriptor = BeanUtils.getPropertyDescriptor(sheetClass, field.getName());
        if (propertyDescriptor == null && field.getName().startsWith("is") && (field.getType() == Boolean.class || field.getType() == boolean.class)) {
            System.out.println(field.getName());
            propertyDescriptor = BeanUtils.getPropertyDescriptor(sheetClass, field.getName().substring(2));
        }
        if (propertyDescriptor != null) {
            try {
                return propertyDescriptor.getReadMethod().invoke(target);
            } catch (IllegalAccessException | InvocationTargetException e) {
                LOGGER.error(e.getMessage(), e);
                throw new RuntimeException(e);
            }
        }
        // not found propertyDescriptor or an exception happened when invoking
        return null;
    }

    /**
     * 参数格式化为String
     */
    private static String formatValue(Field field, Object value) {
        Class<?> fieldType = field.getType();

        ExcelField excelField = field.getAnnotation(ExcelField.class);
        if (value == null) {
            return null;
        }

        if (Boolean.class.equals(fieldType) || Boolean.TYPE.equals(fieldType)) {
            String[] boolValues = excelField.boolValues().split(",");
            return (Boolean) value ? boolValues[0] : boolValues[1];
        } else if (String.class.equals(fieldType)) {
            return String.valueOf(value) + excelField.suffix();
        } else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
            return String.valueOf(value) + excelField.suffix();
        } else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
            return String.valueOf(value) + excelField.suffix();
        } else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
            return String.valueOf(value) + excelField.suffix();
        } else if (BigDecimal.class.equals(fieldType)) {
            return String.valueOf(value) + excelField.suffix();
        } else if (Date.class.equals(fieldType)) {
            SimpleDateFormat dateFormat = new SimpleDateFormat(excelField.dateFormat());
            return dateFormat.format(value) + excelField.suffix();
        } else if (IDictEnum.class.isAssignableFrom(fieldType)) {
            return ((IDictEnum)value).getDescription();
        } else {
            throw new RuntimeException("request illegal type, type must be Integer not int Long not long etc, type=" + fieldType);
        }
    }

    /**
     * 创建导出的表名
     * @param filenamePrefix 表名前缀
     * @return
     */
    public static String createFileName(String filenamePrefix) {
        return createFileName(filenamePrefix, new Date());
    }

    public static String excelAttachmentFileHeaderValue(String fileName) {
        return "attachment;filename=" + fileName;
    }

    /**
     * 创建导出的表名
     * @param filenamePrefix 表名前缀
     * @param date excel文件的时间
     * @return
     */
    public static String createFileName(String filenamePrefix, Date date) {
        return  (filenamePrefix + "_" + DateUtils.format(date, EXCEL_NAME_DATE_PATTERN) + ".xlsx");
    }

    /**
     * 自适应宽度(中文支持)
     * @param sheet
     * @param size
     */
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (length > 255){
                            length = 255;
                        }
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    public static class ExcelStyle {

        private Workbook workbook;

        private Sheet sheet;

        ExcelStyle(Workbook workbook, Sheet sheet) {
            this.workbook = workbook;
            this.sheet = sheet;
        }

        /**
         * 设置表头的单元格样式
         */
        CellStyle getHeadStyle() {
            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            // 设置单元格的背景颜色为淡蓝色
            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // 设置单元格水平居中对齐
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 设置单元格垂直居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 创建单元格内容显示不下时自动换行
            //cellStyle.setWrapText(true);
            // 设置单元格字体样式
            Font font = workbook.createFont();
            // 设置字体加粗
            font.setBold(true);
            font.setFontName("宋体");
            font.setFontHeight((short) 200);
            cellStyle.setFont(font);
            // 设置单元格边框为细线条
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            return cellStyle;
        }

        /**
         * 设置表体的单元格样式
         */
        CellStyle getBodyStyle() {
            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            // 设置单元格居中对齐
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 设置单元格垂直居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 创建单元格内容显示不下时自动换行
            //cellStyle.setWrapText(true);
            // 设置单元格字体样式
            Font font = workbook.createFont();
            // 设置字体加粗
            font.setBold(true);
            font.setFontName("宋体");
            font.setFontHeight((short) 200);
            cellStyle.setFont(font);
            // 设置单元格边框为细线条
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderTop(BorderStyle.THIN);
            return cellStyle;
        }
    }



    /**
     * 填充既有的Excel表格, 按照一列一列垂直填充，每一列代表一个VO
     * @param list 填充既有表格的数据
     * @param sheetClass 指定的类
     * @param requirement 填充要求
     */
    public static void fillExcelVertically(List<?> list, Class<?> sheetClass, FillRequirement requirement) {
        Workbook workbook = null;
        OutputStream outputStream = null;
        InputStream inputStream = null;
        try {
            /**
             * 谨慎使用{@link WorkbookFactory#create(File)}
             * 当调用{@link WorkbookFactory#create(File)}时，此时POI内部已经打开了文件输出流OutputStream，
             *
             * 如果再次打开了文件输出流，会抛出异常{@link org.apache.poi.openxml4j.exceptions.OpenXML4JException}
             * 无法执行workbook.write(outputStream)， 目前并不清楚此方法如何写入数据到文件，用来读取文件倒是没有问题
             */
            inputStream = FileUtils.openInputStream(requirement.getTargetFile());
            workbook = WorkbookFactory.create(inputStream);
            /* ---- 开始填充 ---- */
            Sheet sheet = workbook.getSheetAt(requirement.getUseSheetNum());
            // 提取配有注解的字段
            List<Field> fields = getFieldsWithAnnotation(sheetClass);

            // 当前正在操作的行
            Row currentRow;
            // 共fields.size()行
            for (int rowIndex = 0; rowIndex < fields.size(); rowIndex++) {
                Field field = fields.get(rowIndex);
                currentRow = sheet.getRow(rowIndex + requirement.getStartRow());

                // 当前正在操作的列
                Cell currentCell;
                // 共list.size()列
                int minColumnIndex = requirement.getStartColumn();
                int maxColumnIndex = getMaxColumnIndex(requirement, list.size());
                int listIndex = 0;
                for (int columnIndex = minColumnIndex; columnIndex <= maxColumnIndex; columnIndex++) {
                    if (requirement.getSkipColumns() != null && requirement.getSkipColumns().contains(columnIndex)) {
                        continue;
                    }
                    currentCell = currentRow.getCell(columnIndex);
                    currentCell.setCellType(CellType.STRING);
                    // 已有值并且为 “/” 不处理
                    if (StringUtils.isNotBlank(currentCell.getStringCellValue()) && currentCell.getStringCellValue().contains("/")) {
                        continue ;
                    }

                    // 得到要填充的数据
                    Object fieldValue = getFieldValue(sheetClass, field,list.get(listIndex));
                    String value = formatValue(field, fieldValue);

                    if (isNumberType(field.getType())) {
                        try {
                            currentCell.setCellValue(Double.valueOf(value));
                        } catch (Exception e) {
                            currentCell.setCellValue(value);
                        }
                    } else {
                        currentCell.setCellValue(value);
                    }

                    listIndex++;
                }
            }

            // 执行excel中的所有公式计算（POI写入单元格的内容不是Number类型，而是其他类型时，计算是不会执行的）
            XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
            outputStream = FileUtils.openOutputStream(requirement.getTargetFile());
            workbook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error("fillExcelVertically executed failed", e);
        } finally {
            IOUtils.closeQuietly(inputStream, outputStream, workbook);
        }
    }

    private static int getMaxColumnIndex(FillRequirement requirement, int dataSize) {
        int minColumnIndex = requirement.getStartColumn();
        int maxColumnIndex = minColumnIndex + dataSize - 1;
        if (requirement.getSkipColumns() != null && !requirement.getSkipColumns().isEmpty()) {
             Collections.sort(requirement.getSkipColumns(), (n1, n2) -> n1 - n2);
            for (int columnIndex : requirement.getSkipColumns()) {
                if (columnIndex >= minColumnIndex && columnIndex <= maxColumnIndex) {
                    maxColumnIndex++;
                }
            }
        }
        return maxColumnIndex;
    }

    private static boolean isNumberType(Class<?> type) {
        if (Number.class.isAssignableFrom(type) || Byte.TYPE.equals(type) || Short.TYPE.equals(type)
                || Integer.TYPE.equals(type) || Long.TYPE.equals(type) || Double.TYPE.equals(type) || Float.TYPE.equals(type)) {
            return true;
        } else {
            return false;
        }

    }

    // Excel填表要求 类啦
    public class FillRequirement {


        // 使用的sheet序号，0表示第一个sheet
        private int useSheetNum;

        // 填充目标，Excel文件
        private File targetFile;

        // 填充开始的列 0表示从第一列爱是
        private int startColumn;

        // 填充开始的行 0表示从第一行开始
        private int startRow;

        // 跳过不填写的列数集合
        private List<Integer> skipColumns;

        public int getUseSheetNum() {
            return useSheetNum;
        }

        public FillRequirement setUseSheetNum(int useSheetNum) {
            this.useSheetNum = useSheetNum;
            return this;
        }

        public File getTargetFile() {
            return targetFile;
        }

        public FillRequirement setTargetFile(File targetFile) {
            this.targetFile = targetFile;
            return this;
        }

        public int getStartColumn() {
            return startColumn;
        }

        public FillRequirement setStartColumn(int startColumn) {
            this.startColumn = startColumn;
            return this;
        }

        public int getStartRow() {
            return startRow;
        }

        public FillRequirement setStartRow(int startRow) {
            this.startRow = startRow;
            return this;
        }

        public List<Integer> getSkipColumns() {
            return skipColumns;
        }

        public FillRequirement setSkipColumns(List<Integer> skipColumns) {
            this.skipColumns = skipColumns;
            return this;
        }
    }

    public static class ExcelCellData {

        /**
         * row to get, the logical row ( 0-based)
         */
        private int rownum;

        /**
         * (0 based) index
         */
        private int cellnum;

        private BigDecimal numberData;

        private String stringData;

        public int getRownum() {
            return rownum;
        }

        public void setRownum(int rownum) {
            this.rownum = rownum;
        }

        public int getCellnum() {
            return cellnum;
        }

        public void setCellnum(int cellnum) {
            this.cellnum = cellnum;
        }

        public BigDecimal getNumberData() {
            return numberData;
        }

        public void setNumberData(BigDecimal numberData) {
            this.numberData = numberData;
        }

        public String getStringData() {
            return stringData;
        }

        public void setStringData(String stringData) {
            this.stringData = stringData;
        }
    }

    public static void fillExcelVertically(List<ExcelCellData> cellDataList, File file, int sheetIndex) {
        if (cellDataList == null || cellDataList.isEmpty()) {
            return;
        }
        Workbook workbook = null;
        InputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            // Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable
            inputStream = new FileInputStream(file);
            workbook = WorkbookFactory.create(inputStream);


            Sheet sheet = workbook.getSheetAt(sheetIndex);
            for(ExcelCellData cellData : cellDataList) {
                Cell currentCell = sheet.getRow(cellData.getRownum()).getCell(cellData.getCellnum());
                if (currentCell == null) {
                    System.out.println("Null cell" + cellData.getRownum()  + "column " + cellData.getCellnum());
                    continue;
                }
                if (cellData.getNumberData() == null) {
                    currentCell.setCellValue(cellData.getStringData());
                } else {
                    currentCell.setCellValue(cellData.getNumberData().doubleValue());
                }
            }

            // Loops over all cells in all sheets of the supplied workbook
            // For cells that contain formulas, their formulas are evaluated, and the results are saved. These cells remain as formula cells
            XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
            outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error("fillExcelVertically executed failed", e);
        } finally {
            IOUtils.closeQuietly(inputStream, outputStream, workbook);
        }
    }

    /**
     * 读取CSV文件数据，返回结果以表头作为key
     * @param file
     * @return
     */
    public static List<Map<String, Object>> readCsv(File file) {
        List<Map<String, Object>> rawData = new ArrayList<>();
        BufferedReader bufferedReader = null;
        try {
            // 设定UTF-8字符集，使用带缓冲区的字符输入流BufferedReader读取文件内容
            bufferedReader = new BufferedReader(new InputStreamReader(FileUtils.openInputStream(file)));
            // 表头所在的行
            String headLine = bufferedReader.readLine();
            String[] head = StringUtils.splitByWholeSeparatorPreserveAllTokens(headLine, ",");

            // 遍历数据行并存储在名为records的ArrayList中，每一行records中存储的对象为一个String数组
            String record;
            while ((record = bufferedReader.readLine()) != null) {
                String fields[] = StringUtils.splitByWholeSeparatorPreserveAllTokens(record, ",");
                Map<String, Object> map = new HashMap<>(fields.length);
                for (int i = 0; i < fields.length; i++) {
                    String key = head.length > i ? head[i] : "";
                    map.put(key, fields[i]);
                }
                rawData.add(map);
            }

        } catch (IOException e) {
            LOGGER.error("Fail to read csv file", e);
        } finally {
            IOUtils.closeQuietly(bufferedReader);
        }
        return rawData;
    }
}
